package com.example.sisko_database;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.widget.RatingBar;

import com.example.queryObjects.*;


public class DatabaseQuery {
	
	private DatabaseInit myDbHelper;
	private SQLiteDatabase database;
	
	private String ratingID;
	private String contactID;
	private String menu; 
	private String phoneNr;
	
	//opening the database
	public DatabaseQuery(Context context) {
		myDbHelper = new DatabaseInit(context);
	
		try {
		  
			myDbHelper.createDataBase();
		  
		} catch (IOException ioe) {
		
			throw new Error("Unable to create database");
		  
		}
		  
		try {
		  
			database = myDbHelper.openDataBase();
		
		  
		}catch(SQLException sqle){
		  
			throw sqle;
		 
		}
	}
	
	//closing the database
	public void close(){
		database.close();
	}
	
	//query for getting the names (Restaurant, Bar, Cafe, Club) out of the database
	public List<ViewModel> getNames(String category) {
	    List<ViewModel> modelList = new ArrayList<ViewModel>();
	 
	    Cursor cursor = database.rawQuery("SELECT "+ "loc_id, name" 
	            +" FROM "+ "TABLE_LOCATION" 
	    		+" WHERE " + "category" + "=" + category
	    		+" ORDER BY "+ "name" +" COLLATE NOCASE;", null);
	    
	    // looping through all rows and adding to list
	    if (cursor.moveToFirst()) {
	        do {
	        	ViewModel model = new ViewModel();
	        	model.setLocID(Integer.parseInt(cursor.getString(0)));
	        	model.setName(cursor.getString(1));
	            // Adding models to list
	            modelList.add(model);
	        } while (cursor.moveToNext());
	    }
	 
	    // return model list
	    return modelList;
	}
	
	//query for the information like opening hours, name, address, city, path of the picture and menu.
	public Restaurant getInfo(String loc_id){
		
		Cursor cursor2 = database.query("TABLE_LOCATION", new String[] { "name", "picture", "menu", "contact_id", "rating_id", "category"  }, 
				"loc_id" + "=" + "\"" +  loc_id + "\"", null, null, null, null, null);
		
		Restaurant res = new Restaurant();
		
		res.setLoc_id(Integer.parseInt(loc_id));
		
		if (cursor2.moveToFirst()) {
		        do {
		        	res.setName(cursor2.getString(0));
		      	    res.setPicture(cursor2.getString(1));
		      	    res.setMenu(cursor2.getString(2));
		      	    contactID=cursor2.getString(3);
		      	    ratingID=cursor2.getString(4);
		      	    res.setCategory(cursor2.getString(5));
		      	    res.setRating_id(Integer.parseInt(cursor2.getString(4)));
		      	    
		        }while (cursor2.moveToNext());
		}
		
		Cursor cursor = database.query("TABLE_CONTACT", new String[] { "longitude",
		        "latitutde", "street", "house_number", "zip", "city", "phone", "hours" }, "contact_id" + "=" + "\"" + contactID + "\"",
		        null, null, null, null, null);
		
		
		        
		if (cursor.moveToFirst()) {
		        do {
		        	res.setLng(Double.parseDouble(cursor.getString(0)));
		    	    res.setLat(Double.parseDouble(cursor.getString(1)));
		    	    res.setStreet(cursor.getString(2));
		    	    if(cursor.getString(3)==null){
		    	    	res.setHouse_number(0);
		    	    }else{
		    	    res.setHouse_number(Integer.parseInt(cursor.getString(3)));
		    	    }
		    	    res.setZip(cursor.getString(4));
		    	    res.setCity(cursor.getString(5));
		    	    res.setTelephone(cursor.getString(6));
		    	    res.setHours(cursor.getString(7));
		        }while (cursor.moveToNext());
		}
	   
		Cursor cursor3 = database.query("TABLE_RATING", new String[] { "rating_bar" }, "rating_id" + "=" + "\"" + ratingID + "\"",
		        null, null, null, null, null);
		
		if (cursor3.moveToFirst()) {
			    do {
			    	res.setRatingBar(Float.parseFloat(cursor3.getString(0)));
			    }while (cursor3.moveToNext());
		}
		
        return res;
	}
	
	//query returns all favorites which have a "1" (YES) in the favorite column
	public FavoriteModel getFavoriteStatus(String loc_id){
		
		Cursor cursor = database.query("TABLE_LOCATION", new String[] { "rating_id" }, 
				"loc_id" + "=" + "\"" +  loc_id + "\"", null, null, null, null, null);
		
		FavoriteModel fav = new FavoriteModel();
		
		if (cursor.moveToFirst()) {
	        do {ratingID=cursor.getString(0);
	        
	        }while (cursor.moveToNext());
		}
	        
	    Cursor cursor2 = database.query("TABLE_RATING", new String[] { "favorit" }, "rating_id"
	    + "=" + "\"" + ratingID + "\"",
			        null, null, null, null, null);
		
	    if (cursor2.moveToFirst()) {
		    do {
		        	fav.setFavorite(Integer.parseInt(cursor2.getString(0)));
		    }while (cursor2.moveToNext());
		        
	     } 
	     
	     return fav;
		
	     }
	
	//query returns all favorites in the favorite window, which have a "1" (YES) in the favorite column 
	public List<ViewModel> getFavList() {
	    List<ViewModel> modelList = new ArrayList<ViewModel>();
	    List<String> rating = new ArrayList<String>();
	    
	    Cursor cursor = database.query("TABLE_RATING", new String[] {"rating_id"}
	    , "favorit" + "=" + "\"1\"",
        null, null, null, null, null);
	    
	    String current_rating;
	    
	    if (cursor.moveToFirst()) {
		    do {
		        current_rating = cursor.getString(0);
		        rating.add(current_rating);
		    }while (cursor.moveToNext());
		    Log.w("Size", String.valueOf(rating.size()));
		        
	     } 
	    for (int i=0; i<rating.size(); i++){
	    	
	    	Cursor cursor2 = database.rawQuery("SELECT "+ "loc_id, name, category" 
		            +" FROM "+ "TABLE_LOCATION" 
		    		+" WHERE " + "loc_id" + "=" + "\""+ rating.get(i) + "\""
		    		+" ORDER BY "+ "name" +" COLLATE NOCASE;", null);
	    
		    // looping through all rows and adding to list
		    if (cursor2.moveToFirst()) {
		        do {
		        	ViewModel model = new ViewModel();
		        	model.setLocID(Integer.parseInt(cursor2.getString(0)));
		        	model.setName("[" + cursor2.getString(2) + "]  " + cursor2.getString(1));
		            // Adding models to list
		            modelList.add(model);
		        } while (cursor2.moveToNext());
		    }
	    }
	 
	    // return model list
	    return modelList;
	}
		
	//query to find out if there is an entry in the menu column
	public boolean checkMenu(int Loc_ID){
			
		Cursor cursor =database.query("TABLE_LOCATION", new String[] { "menu"}, "loc_id"
	    		+ "=" + "\""+String.valueOf(Loc_ID)+"\"", null, null, null, null, null);
		
		
		if(cursor.moveToFirst()){
			do{
				menu= cursor.getString(0);
			}while(cursor.moveToNext());
			
		}
		
		if(menu ==null){
			
			return false;
		}else{
			return true;			
		}

	}
	
	//query to find out if there is an entry in the phone column
	public boolean checkPhone(int loc_id){
			
		Cursor cursor =database.query("TABLE_LOCATION", new String[] { "contact_id"}, "loc_id"
	    		+ "=" + "\""+String.valueOf(loc_id)+"\"", null, null, null, null, null);
					
		if(cursor.moveToFirst()){
			do{
				contactID=cursor.getString(0);
			}while(cursor.moveToNext());
		}
		
		Cursor cursor2 = database.query("TABLE_CONTACT", new String[] { "phone"}, "contact_id"
	    		+ "=" + "\""+contactID+"\"", null, null, null, null, null);
		
		if(cursor2.moveToFirst()){
			do{
				phoneNr=cursor2.getString(0);
			}while(cursor2.moveToNext());
		}
		
		if(phoneNr==null){
			return false;
		}else{
			return true;
		}
			
	}
		
	//getting rating_id and contact_id in order to delete one entry in every table
	public Restaurant getID(int loc_id){
			
		Cursor cursor2 = database.query("TABLE_LOCATION", new String[] { "rating_id, contact_id"}, 
				"loc_id" + "=" + "\""+ loc_id +"\"", null, null, null, null, null);
		
		Restaurant ID = new Restaurant ();
		
		if(cursor2.moveToFirst()){
			do{
				ID.setRating_id(Integer.parseInt(cursor2.getString(0)));
				ID.setContact_id(Integer.parseInt(cursor2.getString(0)));
			}while(cursor2.moveToNext());
		}
		return ID;
	}
		
	
}
